package cn.pku.ss.sirx.sna.ib.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.LinkedList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;

import cn.pku.ss.sirx.sna.ib.bean.Influence;
import cn.pku.ss.sirx.sna.ib.bean.Message;
import cn.pku.ss.sirx.sna.ib.util.Util;

/**
 * Access database to retrieve message board information.
 * 
 * @author SirX
 * @version 1.0
 */
public class InfluenceDao {
	static {
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static InfluenceDao getInstance() {
		return instance;
	}

	/**
	 * Get the ids of message board owners.
	 * 
	 * @return the ids of message board owners; or null if exception occurs
	 */
	public List<String> getMeesageBoardOwnerIds() {
		String sqlSentence = "select UserId from Message group by UserId";
		Connection connection = this.getConnection();
		try {
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(sqlSentence);
			List<String> result = new LinkedList<String>();
			while (resultSet.next()) {
				String id = resultSet.getString(1);
				if (id != null) {
					result.add(id);
				}
			}
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			this.closeConnection(connection);
		}
	}

	/**
	 * Get the collection of messages in a message board.
	 * 
	 * @param messageBoardId
	 *            message board identity
	 * @return collection of messages in a message board
	 */
	@SuppressWarnings("unchecked")
	public Collection<Message> getMessages(String messageBoardId) {
		Session session = PersistenceSystem.getInstance().openSession();
		Query query = session
				.createQuery("from cn.pku.ss.sirx.sna.ib.bean.Message message where message.messageBoardId = ?");
		query.setString(0, messageBoardId);
		Collection<Message> result = query.list();
		session.close();
		return result;
	}

	/**
	 * Update influences in database.
	 * 
	 * @param influenceCollection
	 *            a collection of influences to update
	 */
	public void updateInfluence(Collection<Influence> influenceCollection) {
		PersistenceSystem persistenceSystem = PersistenceSystem.getInstance();
		for (Influence influence : influenceCollection) {
			try {
				Influence old = this.getInfluence(influence.getInfluencerId(),
						influence.getInfluenceeId(), influence.getDate());
				if (old == null) {
					persistenceSystem.saveObject(influence);
				} else {
					old.setValue(old.getValue() + influence.getValue());
					persistenceSystem.updateObject(old);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * Get an influence by some properties.
	 * 
	 * @param influencerId
	 *            identity of influencer
	 * @param influenceeId
	 *            identity of influencee
	 * @param date
	 *            the date when influence occurs
	 * @return an Influence instance
	 */
	@SuppressWarnings("unchecked")
	public Influence getInfluence(String influencerId, String influenceeId,
			Date date) {
		Calendar calendar = new GregorianCalendar();
		calendar.setTime(Util.filterTime(date));
		Session session = PersistenceSystem.getInstance().openSession();
		Query query = session
				.createQuery("from cn.pku.ss.sirx.sna.ib.bean.Influence influence where influence.influencerId = ? and influence.influenceeId = ? and influence.date = ?");
		query.setString(0, influencerId);
		query.setString(1, influenceeId);
		query.setCalendar(2, calendar);
		List<Influence> resultList = query.list();
		Influence result = resultList.isEmpty() ? null : resultList.get(0);
		session.close();
		return result;
	}

	// ////////////////////////////////////////////////////////////////////////////

	private InfluenceDao() {
		// create table
		String sqlSentence = "create table Influence (Id int not null primary key, InfluencerId nvarchar(50), InfluenceeId nvarchar(50), Date datetime, Value float)";
		Connection connection = this.getConnection();
		try {
			Statement statement = connection.createStatement();
			statement.executeUpdate(sqlSentence);
		} catch (SQLException e) {
			// do nothing because exception means table has existed
		} finally {
			this.closeConnection(connection);
		}
	}

	private Connection getConnection() {
		try {
			return DriverManager.getConnection(url);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	private void closeConnection(Connection connection) {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// ////////////////////////////////////////////////////////////////////////////

	private static InfluenceDao instance = new InfluenceDao();
	private String url = "jdbc:sqlserver://localhost:1433;integratedSecurity=true;DatabaseName=Xiaonei";
}
